package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import tools.DBTools;
import tools.MyCode;
import entity.User;

public class UserDAO {
	//该方法判断用户表中是否已有某个用户名
	public boolean haveName(String name) {
		boolean re=false;
		 Connection conn = DBTools.getInstance().getConnection();
		String sql="select count(*) from TBL_USER_ACCOUNT where LOGIN_ID=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			rs=ps.executeQuery();
			rs.next();
			if(rs.getInt(1)>0) {
				re=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return re;
	}
//	该方法通过用户名获得密码
	public String getPassByName(String name) {
		String pass=null;
		 Connection conn = DBTools.getInstance().getConnection();
		String sql="select PASSWORD from TBL_USER_ACCOUNT where LOGIN_ID=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				pass=rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
		DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return pass;
	}
//	该方法在数据库中注册一个新用户，需要传递过来一个User对象
	public int addUser(User user) {
		int re=MyCode.FAIL;
		 Connection conn = DBTools.getInstance().getConnection();
		String sql="insert into TBL_USER_ACCOUNT(id, login_id, password) values(sq_id.nextval,?,?)";
		PreparedStatement ps = null;
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, user.getLoginId());
			ps.setString(2, user.getPassword());
			boolean dbre=ps.execute();
			if(!dbre) {
				re=MyCode.SUCCESS;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTools.getInstance().closeConnection(conn, ps, null);
		}
		return re;
	}
	
//	该方法通过用户id获得密码
	public String getPassByUserId(int userId) {
		String pass=null;
		 Connection conn = DBTools.getInstance().getConnection();
		String sql="select PASSWORD from TBL_USER_ACCOUNT where ID=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs=ps.executeQuery();
			if(rs.next()) {
				pass=rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return pass;
	}
	
//	public int addUser(User user) {
//		int re=MyCode.FAIL;
//		DBTools db=new DBTools();
//		Connection conn=db.getConnection();
//		String sql="insert into TBL_USER_ACCOUNT values(sq_id.nextval,?,?,?,?,?,'男',SYSDATE,'aaa',?,?,0,'O','user','common')";
//		try {
//			PreparedStatement ps=conn.prepareStatement(sql);
//			ps.setString(1, user.getLoginId());
//			ps.setString(2, user.getPassWord());
//			ps.setString(3, user.getTradPassWord());
//			ps.setString(4, user.getNickName());
//			ps.setString(5, user.getRealName());
//			ps.setString(6, user.getIdentityCard());
//			ps.setInt(7, user.getTelphone());
//			boolean dbre=ps.execute();
//			if(!dbre) {
//				re=MyCode.SUCCESS;
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}finally {
//			db.closeConnection(conn);
//		}
//		return re;
//	}
	public int getIdByName(String name) {
		int re=0;
		 Connection conn = DBTools.getInstance().getConnection();
		String sql="select ID from TBL_USER_ACCOUNT where LOGIN_ID=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				re=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return re;
	}
	
	public String getTypeByUserId(int userId) {
		String userType = "";
		 Connection conn = DBTools.getInstance().getConnection();
		String sql="select USER_TYPE from TBL_USER_ACCOUNT where ID=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs=ps.executeQuery();
			rs.next();
			userType = rs.getString("USER_TYPE");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTools.getInstance().closeConnection(conn, ps, rs);
		}
		return userType;
	}
}
